// knex是对sql的封装
// 避免直接写sql语句造成sql注入风险
// 简化sql语句
import knex from 'knex'
import fs from 'node:fs'
import jsYaml from 'js-yaml'
import express from 'express'

const dbYaml = fs.readFileSync('../db.config.yaml', 'utf-8')

const dbConfig = jsYaml.load(dbYaml)
console.log('dbConfig',dbConfig);
const sql = knex({
    client: 'mysql2',
    connection: dbConfig.db
})

const app = express()
app.use(express.json())
const port = 3001
// 查
app.get('/list', async (req, res) => {
    // const [data] = await sql.query('select * from user')
    const result = await sql.select().table('user')
    console.log('result',result);
    res.send(result)
})
// 单个查
app.get('/get/:userId', async (req, res) => {
    const {userId} = req.params
    const result = await sql('user').where({id: userId}).select()
    res.send(result)
})
// 增
app.post('/add', async (req, res) => {
    console.log('req.body',req.body);
    const {name, age, hobby, address} = req.body
    // const [data] = sql.query('insert into product(name,user_id) values(?,?)', [xx,xx])
    const result = await sql('user').insert({name, age, hobby, address})
    res.send(result)
})
// 改
app.post('/update',async (req, res) => {
    const {name, age, hobby, address, id} = req.body
    console.log(name, age, hobby, address, id);
    // const result = await sql.query(`update user set name=?,age=?,hobby=?,address=? where id=?`, [name, age, hobby, address, id])
    const result =await sql('user')
    .where({id})
    .update({name, age, hobby, address})
    console.log('result',result);
    res.send({
        code: 200,
        msg: 'success'
    })
})
// 删
app.post('/del/:userId', async (req,res) => {
    const {userId} = req.params
    // const [data] = await sql.query('delete from user where id=?', [userId])
    const result = await sql('user').where({id: userId}).delete()
    res.json({
        code: 200,
        message: 'delete success' + result
    })
})

app.listen(port, () => {
    console.log(`server is runing at ${port}`);
})